package cn.wubo.excel.controller;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.sql.*;

@Controller
public class IndexController {

    @RequestMapping("/")
    public String downloadExcel() {
        return "redirect:/index";
    }

    @RequestMapping("/index")
    public String index() {
        return "index";
    }

    @RequestMapping(value = "/downloadExcel", method = RequestMethod.POST)
    public ResponseEntity downloadExcel(@RequestParam(value = "dbtype", required = true) String dbtype,
                                        @RequestParam(value = "uri", required = true) String uri,
                                        @RequestParam(value = "username", required = true) String username,
                                        @RequestParam(value = "password", required = true) String password,
                                        @RequestParam(value = "sql", required = true) String sql) {

        String tempFileName = System.currentTimeMillis() + ".xlsx";
        try {
            Connection conn = this.getConn(dbtype.toUpperCase(), uri, username, password);
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int col = rsmd.getColumnCount();

            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet("导出数据");

            XSSFRow titleRow = sheet.createRow(0);
            for (int i = 0; i < col; i++) {
                XSSFCell cell1 = titleRow.createCell(i);
                cell1.setCellValue(rsmd.getColumnName(i + 1));
            }

            int rowNo = 1;
            while (rs.next()) {
                XSSFRow row = sheet.createRow(rowNo);
                for (int i = 0; i < col; i++) {
                    XSSFCell cell1 = row.createCell(i);
                    cell1.setCellValue(rs.getString(i + 1));
                }
                rowNo++;
            }

            /*System.out.println("============================");
            while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 2) && (rs.getString(i).length() < 8)) {
                        System.out.print("\t");
                    }
                }
                System.out.println("");
            }
            System.out.println("============================");*/
            pstmt.close();
            conn.close();

            File tempFile = new File("./file/" + tempFileName);
            FileOutputStream outputStream = new FileOutputStream(tempFile);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException | SQLException | IOException e) {
            e.printStackTrace();
            return ResponseEntity.
                    badRequest().
                    body(e.getMessage());
        }

        File file = new File("./file/" + tempFileName);
        InputStream is = null;
        try {
            is = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        InputStreamResource resource = new InputStreamResource(is);

        HttpHeaders headers = new HttpHeaders();
        //headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
        //headers.add("Pragma", "no-cache");
        //headers.add("Expires", "0");
        //headers.setContentDispositionFormData("attachment", URLEncoder.encode("123.txt", "UTF-8"));
        headers.setContentType(MediaType.parseMediaType("application/octet-stream"));
        headers.setContentDispositionFormData("attachment", file.getName());
        headers.setContentLength(file.length());

        return ResponseEntity
                .ok()
                .headers(headers)
                .body(resource);
    }

    private Connection getConn(String dbtype, String uri, String username, String password) throws ClassNotFoundException, SQLException {
        switch (dbtype) {
            case "SQLSERVER":
                Class.forName("com.mysql.jdbc.Driver");
                break;
            case "ORACLE":
                Class.forName("oracle.jdbc.driver.OracleDriver");
                break;
            default:
        }
        Connection conn = DriverManager.getConnection(uri, username, password);
        return conn;
    }

    @RequestMapping("/upload")
    public String upload() {
        return "upload";
    }

    @RequestMapping(value = "/uploadTemplate", method = RequestMethod.POST)
    @ResponseBody
    public String uploadTemplate(@RequestParam("file") MultipartFile uploadfile) throws IOException {
        String path = uploadfile.getOriginalFilename();
        System.out.println(path);
        File in = new File(path);
        if(in.exists())
            in.delete();
        in.createNewFile();

        FileOutputStream fos = new FileOutputStream(in);
        InputStream is = uploadfile.getInputStream();

        byte[] bytes = new byte[1024];
        int len = -1;
        while((len = is.read(bytes)) != -1) {
            fos.write(bytes, 0, len);
        }

        fos.flush();
        fos.close();

        return "上传成功！";
    }
}
